﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using QLTV_V2.DataAccess;
using QLTV_V2.BussinessObject;

namespace QLTV_V2.BussinessLogic
{
    class DanhMucBUS
    {
        Utilities uti = new Utilities();
        ConnectData conn = new ConnectData();
        string sql;
     
        #region Loai Sach
        public LoaiSach GetInfoLoaiSach(string maLS)
        {
            LoaiSach ls = new LoaiSach();
            sql = "select * from LoaiSach where MaLoaiSach='" + maLS + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            ls.MaLoaiSach = dr["MaLoaiSach"].ToString();
            ls.TenLoaiSach = dr["TenLoaiSach"].ToString();
            ls.GhiChu = dr["GhiChu"].ToString();
            return ls;
        }
        public bool InsertLoaiSach(LoaiSach LS)
        {
            sql = "insert into LoaiSach(MaLoaiSach, TenLoaiSach, GhiChu) values(N'" + LS.MaLoaiSach + "',N'" + LS.TenLoaiSach + "',N'" + LS.GhiChu + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        public bool UpdateLoaiSach(string maLS, LoaiSach LS)
        {
            sql = "update LoaiSach set MaLoaiSach=N'" + LS.MaLoaiSach + "', TenLoaiSach=N'" + LS.TenLoaiSach + "', GhiChu=N'" + LS.GhiChu + "' where MaLoaiSach=N'" + maLS + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion
      
        #region Nha Xuất bản
        public NhaXuatBan GetInfoNXB(string maNXB)
        {
            NhaXuatBan nxb = new NhaXuatBan();
            sql = "select * from NhaXuatBan where MaNXB='" + maNXB + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            nxb.MaNXB = dr["MaNXB"].ToString();
            nxb.TenNXB = dr["TenNXB"].ToString();
            nxb.DiaChiNXB = dr["DiaChiNXB"].ToString();
            nxb.GhiChu = dr["GhiChu"].ToString();
            return nxb;
        }
        public bool InsertNXB(NhaXuatBan NXB)
        {
            sql = "insert into NhaXuatBan(MaNXB, TenNXB, DiaChiNXB, GhiChu) values(N'"
                + NXB.MaNXB + "',N'" + NXB.TenNXB + "',N'" + NXB.DiaChiNXB + "',N'" + NXB.GhiChu + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        public bool UpdateNXB(string maNXB, NhaXuatBan NXB)
        {
            sql = "update NhaXuatBan set MaNXB=N'" + NXB.MaNXB + "', TenNXB=N'" + NXB.TenNXB + "',DiaChiNXB=N'" + NXB.DiaChiNXB + "', GhiChu=N'" + NXB.GhiChu
                + "' where MaNXB='" + maNXB + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion

        #region Tác giả
        public TacGia GetInfoTacGia(string maTG)
        {
            TacGia tg = new TacGia();
            sql = "select * from TacGia where MaTacGia='" + maTG + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            tg.MaTacGia = dr["MaTacGia"].ToString();
            tg.TenTacGia = dr["TenTacGia"].ToString();
            tg.GhiChu = dr["GhiChu"].ToString();
            return tg;
        }
        public bool InsertTacGia(TacGia TG)
        {
            sql = "insert into TacGia(MaTacGia, TenTacGia, GhiChu) values(N'" + TG.MaTacGia + "',N'" + TG.TenTacGia + "',N'" + TG.GhiChu + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }

        public bool UpdateTacGia(string maTG, TacGia TG)
        {
            sql = "update TacGia set MaTacGia=N'" + TG.MaTacGia + "', TenTacGia='" + TG.TenTacGia + "', GhiChu='" + TG.GhiChu + "' where MaTacGia='" + maTG + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }

        #endregion
     
        #region Ngon ngu
        public NgonNgu GetInfoNgonNgu(string maNN)
        {
            NgonNgu nn = new NgonNgu();
            sql = "select * from NgonNgu where MaNgonNgu='" + maNN + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            nn.MaNgonNgu = dr["MaNgonNgu"].ToString();
            nn.TenNgonNgu = dr["TenNgonNgu"].ToString();
            nn.GhiChu = dr["GhiChu"].ToString();
            return nn;
        }
        public bool InsertNgonNgu(NgonNgu NN)
        {
            sql = "insert into NgonNgu(MaNgonNgu, TenNgonNgu,GhiChu) values(N'" + NN.MaNgonNgu + "',N'" + NN.TenNgonNgu + "',N'" + NN.GhiChu + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }

        public bool UpdateNgonNgu(string maNN, NgonNgu NN)
        {
            sql = "update NgonNgu set MaNgonNgu=N'" + NN.MaNgonNgu + "', TenNgonNgu=N'" + NN.TenNgonNgu + "', GhiChu=N'" + NN.GhiChu + "' where MaNgonNgu='" + maNN + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion
     
        #region Vi tri
        public ViTri GetInfoViTri(string maVT)
        {
            ViTri vt = new ViTri();
            sql = "select * from ViTri where MaViTri='" + maVT + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            vt.MaViTri = dr["MaViTri"].ToString();
            vt.Khu = dr["Khu"].ToString();
            vt.Ke = dr["Ke"].ToString();
            vt.Ngan = dr["Ngan"].ToString();
            vt.GhiChu = dr["GhiChu"].ToString();
            return vt;
        }
        public bool InsertViTri(ViTri VT)
        {
            sql = "insert into ViTri(MaViTri, Khu, Ke, Ngan, GhiChu) values(N'"
                + VT.MaViTri + "',N'" + VT.Khu + "',N'" + VT.Ke + "',N'" + VT.Ngan + "',N'" + VT.GhiChu + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }

        public bool UpdateViTri(string maVT, ViTri VT)
        {
            sql = "update ViTri set MaViTri=N'" + VT.MaViTri + "', Khu=N'" + VT.Khu + "', Ke=N'" + VT.Ke + "', Ngan=N'" + VT.Ngan + "', GhiChu=N'" + VT.GhiChu + "' "
                + "where MaViTri='" + maVT + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion
     
        #region Nha cung cap
        public NhaCungCap GetInfoNCC(string MaNCC)
        {
            NhaCungCap ncc = new NhaCungCap();
            sql = "select * from NhaCungCap where MaNCC='" + MaNCC + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            ncc.MaNCC = dr["MaNCC"].ToString();
            ncc.TenNCC = dr["TenNCC"].ToString();
            ncc.DiaChi = dr["DiaChi"].ToString();
            ncc.SoDT = dr["SoDT"].ToString();
            return ncc;
        }
        public bool InsertNCC(NhaCungCap NCC)
        {
            sql = "insert into NhaCungCap(MaNCC, TenNCC, DiaChi, SoDT) values(N'"
                + NCC.MaNCC + "',N'" + NCC.TenNCC + "',N'" + NCC.DiaChi + "',N'" + NCC.SoDT + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }

        public bool UpdateNCC(string MaNCC, NhaCungCap NCC)
        {
            sql = "update NhaCungCap set MaNCC=N'" + NCC.MaNCC + "', TenNCC=N'" + NCC.TenNCC + "',DiaChi=N'" + NCC.DiaChi + "', SoDT=N'" + NCC.SoDT
                + "' where MaNCC='" + MaNCC + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion
     
        #region Chuc nang
        public ChucNang GetInfoChucNang(string maCN)
        {
            ChucNang cn = new ChucNang();
            string sql = "select * from ChucNang where MaCN='" + maCN + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            cn.MaCN = dr["MaCN"].ToString();
            cn.TenCN = dr["TenCN"].ToString();
            return cn;
        }
        public bool InsertChucNang(ChucNang CN)
        {
            sql = "insert into ChucNang(MaCN, TenCN) values(N'" + CN.MaCN + "',N'" + CN.TenCN + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }

        public bool UpdateChucNang(string maCN, ChucNang CN)
        {
            sql = "update ChucNang set MaCN='" + CN.MaCN + "', TenCN=N'" + CN.TenCN + "' where MaCN='" + maCN + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion
     
        #region Nhom ban doc
        public NhomBanDoc GetInfoNhomBanDoc(string maNhom)
        {
            NhomBanDoc nbd = new NhomBanDoc();
            sql = "select * from NhomBanDoc where MaNhom='" + maNhom + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            nbd.MaNhom = dr["MaNhom"].ToString();
            nbd.TenNhom = dr["TenNhom"].ToString();
            nbd.SLSach = int.Parse(dr["SLSach"].ToString());
            nbd.TGMuon = int.Parse(dr["TGMuon"].ToString());
            nbd.GhiChu = dr["GhiChu"].ToString();
            return nbd;
        }
       
        public bool InsertNhomBanDoc(NhomBanDoc NBD)
        {
            sql = "insert into NhomBanDoc(MaNhom, TenNhom, SLSach, TGMuon, GhiChu) values(N'"
                + NBD.MaNhom + "',N'" + NBD.TenNhom + "','" + NBD.SLSach + "','" + NBD.TGMuon + "',N'" + NBD.GhiChu + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }

        public bool UpdateNhomBanDoc(string maNhom, NhomBanDoc NBD)
        {
            sql = "update NhomBanDoc set MaNhom=N'" + NBD.MaNhom + "', TenNhom=N'" + NBD.TenNhom + "', SLSach='" + NBD.SLSach + "', TGMuon='" + NBD.TGMuon + "', GhiChu=N'" + NBD.GhiChu
                + "' where MaNhom='" + maNhom + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion
    
        #region Nhan vien
        public DataTable getDSNhanVien()
        {
            sql = "select MaNV as MaNV1, TenNV, ChucVu, NgaySinh, GioiTinh, DiaChi,Email,SoDT,AnhCaNhan,MatKhau from NhanVien order by MaNV";
            return conn.GetDataTable(sql);
        }
        public NhanVien GetInfoNhanVien(string maNV)
        {
            NhanVien nv = new NhanVien();
            sql = "select * from NhanVien where MaNV='" + maNV + "'";
            DataRow dr = conn.GetDataTable(sql).Rows[0];
            nv.MaNV = dr["MaNV"].ToString();
            nv.TenNV = dr["TenNV"].ToString();
            nv.ChucVu = dr["ChucVu"].ToString();
            nv.NgaySinh = Convert.ToDateTime(dr["NgaySinh"]);
            if (dr["GioiTinh"].ToString() == "True")
                nv.GioiTinh = 1;
            else
                nv.GioiTinh = 0;
            nv.DiaChi = dr["DiaChi"].ToString();
            nv.Email = dr["Email"].ToString();
            nv.SoDT = dr["SoDT"].ToString();
            nv.AnhCaNhan = dr["AnhCaNhan"].ToString();
            nv.MatKhau = dr["MatKhau"].ToString();
            return nv;
        }
        public bool InsertNhanVien(NhanVien NV)
        {
            int type = uti.GetTypeDate();
            sql = "insert into NhanVien(MaNV, TenNV, ChucVu, NgaySinh, GioiTinh, DiaChi, Email, SoDT, AnhCaNhan, MatKhau) values(N'"
                + NV.MaNV + "',N'" + NV.TenNV + "',N'" + NV.ChucVu + "', convert(datetime,'" + NV.NgaySinh.ToShortDateString() + "'," + type + ")," + NV.GioiTinh + ",N'" + NV.DiaChi + "',N'"
                + NV.Email + "','" + NV.SoDT + "',N'" + NV.AnhCaNhan + "',N'" + NV.MatKhau + "')";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        public bool UpdateNhanVien(string maNV, NhanVien NV)
        {
            int type = uti.GetTypeDate();
            sql = "update NhanVien set MaNV=N'" + NV.MaNV + "', TenNV=N'" + NV.TenNV + "', ChucVu=N'" + NV.ChucVu + "', NgaySinh=convert(datetime,'" + NV.NgaySinh.ToShortDateString() + "'," + type + "), GioiTinh=" + NV.GioiTinh
                + ", DiaChi=N'" + NV.DiaChi + "', Email=N'" + NV.Email + "', SoDT='" + NV.SoDT + "', AnhCaNhan=N'" + NV.AnhCaNhan + "', MatKhau=N'" + NV.MatKhau
                + "' where MaNV=N'" + maNV + "'";
            if (conn.ExecuteQuery(sql))
                return true;
            return false;
        }
        #endregion
     
        #region Phan quyen
        public bool insertQuyen(int IDQuyen, string manv, string macn)
        {
            sql = "insert into QuyenHan values (" + IDQuyen + ",N'" + manv + "',N'" + macn + "')";
            if (conn.ExecuteQuery(sql) == true)
                return true;
            return false;
        }

        #endregion
    }
}
